﻿prompt 住院国家医保结算清单视图  VW_ZY_YBJSQD_JZB000
create or replace view  VW_ZY_YBJSQD_JZB000
as
-- MODIFICATION HISTORY
-- Person    Date          Comments
-- zhangyc   2022.04.06    created by   ZYSF-20220406-001
-- linzetao  2022.11.01    增加WLTSZT(外联病案推送状态)和WLTSMC(外联病案推送状态名称)字段   ZYSF9-20220921-002
-- zhangyc   2022.12.16    增加YBTYPE, YBJKMC
select
      A.ZYID00 as ZYID00                                         ,--住院ID
      A.BRID00 as BRID00                                         ,--病人ID
      trim(B.ZYH000) as ZYH000                                   ,--住院号
      A.FBBH00 as FBBH00                                         ,--费别编号
      g.YBZXLB as YBZXLB                                         ,--医保中心类别
      trim(g.YBMC00) as YBZXMC                                   ,--医保中心名称
      trim(F.BRXM00) as BRXM00                                   ,--病人姓名
      trim(F.BRXB00) as BRXB00                                   ,--性别
      trim(F.BRCSRQ) as BRCSRQ                                   ,--出生日期
      trim(F.BRMZ00) as BRMZ00                                   ,--民族
      trim(F.BRZJLX) as BRZJLX                                   ,--证件类型
      trim(F.BRZJBH) as BRZJBH                                   ,--证件编号
      trim(F.AAE140) as AAE140                                   ,--险种类型编码
      trim(F.AAE140_MC) as AAE140_MC                             ,--险种类型名称
      trim(F.PSN_NO) as PSN_NO                                   ,--人员编号
      trim(B.RYCWH0) as RYCWH0	                                 ,--病人住院床号
      B.DQBQ00 as DQBQBH                                         ,--当前病人住院病区
      C.BMMC00 as DQBQMC                                         ,--当前病人住院病区名称
      B.DQKS00 as DQKSBH                                         ,--当前病人住院科室
      D.BMMC00 as DQKSMC                                         ,--当前病人住院科室名称
      A.JZDH00 as JZDH00                                         ,--结帐单号
      A.CXJZDH as CXJZDH                                         ,--冲销结账单号
      A.JZRQ00 as JZRQ00                                         ,--结帐日期
      A.JZSJ00 as JZSJ00                                         ,--结帐时间
      decode(trim(A.YBDJH0),'0','',trim(A.YBDJH0)) as YBDJH0     ,--医保单据号
      nvl(decode(trim(A.YBZYH0),'0','',trim(A.YBZYH0)),decode(trim(b.YBZYH0),'0','',trim(b.YBZYH0))) as  YBZYH0,     --医保住院号
      a.JZY000 as 	JZY000                                       ,--结帐操作员编码
      e.ZWXM00 as   JZYXM0                                       ,--结帐操作员姓名
      A.YBJKID as   YBJKID                                       ,--医保接口ID
      A.PJH000 as   PJH000                                       ,--打印的票据流水号
      A.HJJE00 as   HJJE00                                       ,--合计应收金额
      A.TCJJZF as   TCJJZF	                                     ,--统筹基金支付
      A.GRZHZF as   GRZHZF                                       ,--个人医疗帐户支付
      A.ZFJE00 as   ZFJE00	                                     ,--自付金额
      trim(B.DBZBM0) as   DBZBM0                                 ,--单病种编码
      trim(B.DBZMC0) as   DBZMC0                                 ,--单病种名称
      B.RYRQ00 as   RYRQ00                                       ,--入院日期
      B.RYSJ00 as   RYSJ00                                       ,--入院时间
      B.CYRQ00 as   CYRQ00                                       ,--出院日期
      B.CYSJ00 as   CYSJ00                                       ,--出院时间
      b.ZZYS00 as   ZZYS00                                       ,--主治医生员工号
      b.ZZYSXM as   ZZYSXM                                       ,--主治医生姓名
      b.ZRYS00 as   ZRYS00                                       ,--主任医生员工号
      b.ZRYSXM as   ZRYSXM                                       ,--主任医生姓名
      b.SXYS00 as   SXYS00                                       ,--主管医生员工号
      b.SXYSXM as   SXYSXM                                       ,--主管医生员姓名
      substrb(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'0'),1,50) as   JSQDBZ  ,--医保结算单上传标志( 0: 未上传 1:已上传 2:已修改)
      substrb(decode(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'0'),'0','未上传','1','已上传','2','已修改','未知状态'),1,50)   as   JSQDMC ,--清单状态名称
      substrb(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'1'),1,50) as   JSZTBZ  ,--结算状态标志( 0: 已冲销 1:正常)
      substrb(decode(SF_ZY_NATION_JSQDZT(a.ZYID00,a.JZDH00,a.HJJE00,A.CXJZDH,a.YBJKID,a.JSQDBZ,'1'),'0','冲销','1','正常'),1,50)   as   JSZTMC  ,--结算名称
      decode(trim(nvl(b.YBKH00,F.YBKH00)),'0','',trim(nvl(b.YBKH00,F.YBKH00))) as  YBKH00   ,--医保卡号
      decode(trim(nvl(b.YBID00,F.YBID00)),'0','',trim(nvl(b.YBID00,F.YBID00))) as  YBID00   ,--医保ID
      decode(trim(F.XZQH00),'本地','',trim(F.XZQH00)) as  XZQH00                            ,--行政区号
      nvl(g.YBSDLB,'0')                               as   YBSDLB                           ,--医保属地类别；0本地、1省内、2省外
      decode(nvl(g.YBSDLB,'0'),'2','省外','1','省内','本地') as YBSDMC                      ,--医保属地名称 
      decode(sign((select count(1) from BAJK_WLBARZ where ZYID00=a.ZYID00 and YXBZ00='1')),1,'1','0') as WLTSZT, --外联病案推送状态, 1已推送0未推送
      decode(sign((select count(1) from BAJK_WLBARZ where ZYID00=a.ZYID00 and YXBZ00='1')),1,'已推送','未推送') as WLTSMC,  --外联病案推送状态名称, 1已推送0未推送
      nvl(a.YBTYPE,'0') as YBTYPE                                                           ,--医保接口类型[0或空:福建版本 1:国家基线
      
      decode(nvl(a.YBTYPE,'0'),'旧版','1','国家基线版','5','安徽工伤','6','离休接口','7','福建新版工伤','福建本地版')as YBJKMC                                   --医保版本名称
      
 from ZY_JZB000 A,ZY_BRXXB0 B,BM_BMBM00 C,BM_BMBM00 D,BM_YGBM00 E,BM_BRXXB0 F,IC_YBBRLB G
where A.ZYID00 = B.ZYID00
  and A.BRID00 = F.BRID00
  and B.FBBH00 = G.FBBH00
  and B.YBLB00 = G.YBLB00
  and B.DQBQ00 = C.BMBH00
  and B.DQKS00 = D.BMBH00
  and A.JZY000 = E.YGBH00
/

--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%